--USE scm_main;
if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_delete')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_schedule_delete
GO
CREATE PROCEDURE bd_proc_schedule_delete
	@user_serial_str varchar(max),
	@year_month_str varchar(max),
	@ip varchar(200),
	@operator varchar(200)
as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;
	

			CREATE TABLE #user_serial(
				id INT IDENTITY,
				user_serial VARCHAR(50) COLLATE Chinese_PRC_CI_AS
			)
			
			CREATE TABLE #year_month(
				id INT IDENTITY,
				year_month VARCHAR(20) COLLATE Chinese_PRC_CI_AS
			)

			CREATE TABLE #data(
				id INT IDENTITY,
				user_name varchar(50) COLLATE Chinese_PRC_CI_AS,
				user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				year_month varchar(20) COLLATE Chinese_PRC_CI_AS
			)
			

	--  异常扑捉机制
		BEGIN TRY


		-- 开启事务
			BEGIN TRAN
				
				-- 拆分用户编号
				insert into #user_serial
				select * from string_split(iif(isnull(@user_serial_str,'')='','',@user_serial_str),',');

				-- 拆分年月
				insert into #year_month
				select * from string_split(iif(isnull(@year_month_str,'')='','',@year_month_str),',');

				-- 插入数据表
				insert into #data(user_serial,year_month)
				select a.user_serial,b.year_month
				from #user_serial a 
				inner join #year_month b on a.id = b.id
				

				-- 更新用户姓名
				update a set a.user_name = isnull(b.user_lname,'')
				from #data a
				inner join dt_user b on a.user_serial = b.user_serial


				-- 插入考勤日志
				insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,gly_no,sj)  
				select b.user_serial,date,1,6,2,0,0,@operator,getdate()
				from bd_sys_dim_date a
				inner join #data b on CONVERT(varchar(7),a.date,120) = b.year_month
				
				-- 插入操作日志
				insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)
				select 13,0,concat(@operator,'删除',isnull(user_name,''),year_month,'排班'),@operator,getdate(),'',@ip,''
				from #data


				-- 删除明细表数据
				delete a
				from bd_kq_schedule_detail a
				inner join #data b on a.user_serial = b.user_serial and convert(varchar(7),a.shift_date,120) = b.year_month

				-- 删除排班表数据
				delete a
				from kt_paiba a
				inner join #data b on a.user_serial = b.user_serial and a.rq = b.year_month
				
				set @result = @@ROWCOUNT;



				--创建一个游标
				declare my_cursor cursor for select user_serial, concat(year_month,'-',1), DATEADD(DAY,-(DATEPART(DAY,concat(year_month,'-',1))),DATEADD(MONTH,1,concat(year_month,'-',1))) from #data      --这是游标my_cursor的值，这里随便发挥看业务场景

				--打开游标
				open my_cursor                  --没什么好说的

					--变量
					declare @user_serial varchar(50);
					declare @begin_date varchar(50);
					declare @end_date varchar(50);

					--循环游标
					fetch next from my_cursor into @user_serial,@begin_date,@end_date  --获取my_cursor的下一条数据，其中为两个字段分别赋值给@id,@name
					while @@FETCH_STATUS=0 begin --假如检索到了数据继续执行

            -- 调用考勤计算(排班4)
            EXEC bd_proc_timecard_extend @begin_date,@end_date,@user_serial,4

					fetch next from my_cursor into @user_serial,@begin_date,@end_date --获取下一条数据并赋值给变量

				end--关闭释放游标
				close my_cursor
				deallocate my_cursor


		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
			ROLLBACK TRAN;
		END CATCH



--  清除临时表
	drop table #user_serial;
	drop table #year_month;
	drop table #data;

	select @result as result

-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
